package com.newshub.ai.util;

import com.newshub.ai.model.dto.BiQueryRequest;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import java.util.regex.Pattern;

/**
 * SQL 查询构建器服务
 */
@Slf4j
@Service
public class SqlQueryBuilder {

    private static final Pattern HAS_WHERE = Pattern.compile("(?i)\\bWHERE\\b");
    private static final Pattern HAS_ORDER_BY = Pattern.compile("(?i)\\bORDER\\s+BY\\b");
    private static final Pattern HAS_LIMIT = Pattern.compile("(?i)\\bLIMIT\\b");
    private static final Pattern HAS_PUBLISH_TIME = Pattern.compile("(?i)publish_time");

    public String buildSqlFromNaturalLanguage(String query) {
        if (query == null || query.trim().isEmpty()) {
            throw new IllegalArgumentException("查询语句不能为空");
        }
        // TODO: 这里应调用 LLM 生成 SQL；当前回退示例
        return generateSampleSql(query);
    }

    /**
     * 图表类排序：若已包含 ORDER BY，则不再追加，避免别名不一致导致语法错误
     */
    public String buildChartSql(String baseSql, String chartType) {
        if (baseSql == null) return null;
        if (chartType == null || chartType.isBlank()) return baseSql;
        if (HAS_ORDER_BY.matcher(baseSql).find()) return baseSql; // 已包含排序，跳过
        // 保守：不再附加 ORDER BY，以免别名不一致。需要排序时交由 LLM 在 SQL 中完成。
        return baseSql;
    }

    /**
     * 时间过滤：若 SQL 已包含 WHERE/包含 publish_time 或 LLM 已添加，则跳过，避免破坏语法
     */
    public String addTimeFilter(String sql, String timeRange) {
        if (sql == null || timeRange == null || timeRange.isBlank()) return sql;
        if (HAS_PUBLISH_TIME.matcher(sql).find()) return sql; // 已显式过滤时间
        if (HAS_WHERE.matcher(sql).find()) return sql; // 已有 WHERE 条件，保守跳过
        // 保守策略：不主动拼接，交由 LLM 生成，避免插入位置错误（GROUP BY / LIMIT 之后）
        return sql;
    }

    /**
     * 限制返回条数：若已有 LIMIT 则跳过
     */
    public String addLimit(String sql, Integer maxRecords) {
        if (sql == null) return null;
        if (maxRecords == null || maxRecords <= 0) return sql;
        if (HAS_LIMIT.matcher(sql).find()) return sql;
        return sql + " LIMIT " + maxRecords;
    }

    private String generateSampleSql(String query) {
        String lowerQuery = query.toLowerCase();
        if (lowerQuery.contains("分类") || lowerQuery.contains("category")) {
            if (lowerQuery.contains("数量") || lowerQuery.contains("count")) {
                return "SELECT category_name, COUNT(*) AS cnt FROM news WHERE publish_time >= DATE_SUB(NOW(), INTERVAL 7 DAY) GROUP BY category_name ORDER BY cnt DESC LIMIT 100";
            }
        }
        if (lowerQuery.contains("来源") || lowerQuery.contains("source")) {
            if (lowerQuery.contains("数量") || lowerQuery.contains("count")) {
                return "SELECT source_name, COUNT(*) AS cnt FROM news WHERE publish_time >= DATE_SUB(NOW(), INTERVAL 30 DAY) GROUP BY source_name ORDER BY cnt DESC LIMIT 100";
            }
        }
        return "SELECT id, title, publish_time FROM news ORDER BY publish_time DESC LIMIT 50";
    }

    public boolean isValidQuery(String sql) {
        if (sql == null || sql.trim().isEmpty()) return false;
        String lowerSql = sql.toLowerCase();
        String[] dangerousKeywords = {"drop", "delete", "update", "insert", "create", "alter", "truncate"};
        for (String keyword : dangerousKeywords) {
            if (lowerSql.contains(keyword)) {
                log.warn("检测到危险的 SQL 关键字: {}", keyword);
                return false;
            }
        }
        if (!lowerSql.trim().startsWith("select")) {
            log.warn("SQL 必须以 SELECT 开头");
            return false;
        }
        return true;
    }
}
